package com.wanghaicheng.util;

import com.wanghaicheng.util.model.ExcelTableRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

public class ExcelUtil implements Office {

    public static Workbook create(String absolutePath) {
        return defaultCreate(absolutePath);
    }

    public static Workbook create(Workbook workbook) {
        try {
            //根据传入的工作簿类型,创建操作对象
            return workbook.getClass().newInstance();
        } catch (InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
            throw new RuntimeException("无法创建");
        }
    }

    private static Workbook defaultCreate(String absolutePath) {
        String[] split = absolutePath.split("\\.");
        String fileType = split[split.length - 1];
        Workbook sheets;
        try {
            //根据后缀,创建工作簿
            if ("xls".equals(fileType)) {
                sheets = new HSSFWorkbook(new FileInputStream(absolutePath));
            } else if ("xlsx".equals(fileType)) {
                sheets = new XSSFWorkbook(new FileInputStream(absolutePath));
            } else {
                throw new RuntimeException("文件格式不支持");
            }
            return sheets;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }

    public static void replace(Workbook workbook, Map<String, Object> data) {
        //遍历所有工作簿
        for (Sheet sheet : workbook) {
            Drawing<?> patriarch = sheet.createDrawingPatriarch();
            List<ExcelTableRow> rows = new ArrayList<>();
            //遍历每行
            for (Row row : sheet) {
                //遍历每个表格
                for (Cell cell : row) {
                    String value = cell.getStringCellValue();
                    data.forEach((k, v) -> {
                        //根据数据类型,输出数据
                        if (value.equals(k)) {
                            if (v instanceof String) {
                                cell.setCellValue((String) v);
                            } else if (v instanceof Integer) {
                                cell.setCellValue(String.valueOf(v));
                            } else if (v instanceof BigDecimal) {
                                cell.setCellValue(((BigDecimal) v).stripTrailingZeros().toPlainString());
                            } else if (v instanceof LocalDateTime) {
                                cell.setCellValue(v.toString());
                            } else if (v instanceof Date) {
                                cell.setCellValue(v.toString());
                            } else if (v instanceof ByteArrayOutputStream) {
                                patriarch.createPicture(patriarch.createAnchor(
                                                cell.getColumnIndex(), row.getRowNum()
                                                , cell.getColumnIndex() + 1, row.getRowNum() + 1
                                                , cell.getColumnIndex(), row.getRowNum()
                                                , cell.getColumnIndex() + 1, row.getRowNum() + 1),
                                        workbook.addPicture(((ByteArrayOutputStream) v).toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                            } else if (v instanceof Collection) {
                                //列表类型数据
                                int rowCount = cell.getRowIndex();
                                for (Object o : ((Collection) v)) {
                                    rows.add(new ExcelTableRow(rowCount++, cell.getColumnIndex(), (Collection<String>) o));
                                }
                            } else {
                                throw new RuntimeException("数据格式仅支持String(字符串) Integer(数值) bigDecimal(小数) LocalDateTime(日期类型1) Date(日期类型2) ByteArrayOutputStream(图片) Collection(列表数据)");
                            }
                        }
                    });
                }
            }
            //因为上面遍历时无法创建数据,所以在这里插入行数据.如果有,就打印到表格里
            if (rows.size() > 0) {
                for (ExcelTableRow row : rows) {
                    //根据开始行创建一行数据
                    Row sheetRow = sheet.createRow(row.getRowCount());
                    //获取起始列
                    int colCount = row.getColCount();
                    for (String s : row.getData()) {
                        //创建表格,填充数据
                        sheetRow.createCell(colCount++).setCellValue(s);
                    }
                }
            }
        }
    }


    public static void write(Workbook sheets, String absolutePath) {
        try {
            //输出文件
            sheets.write(new FileOutputStream(absolutePath));
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("写出文件失败");
        } finally {
            try {
                sheets.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 从占位符处写入数据
     *
     * @param sheet       表格
     * @param placeholder 占位符
     * @param list        数据
     * @param <T>         数据类型
     */
    public static <T> void write(Sheet sheet, String placeholder, List<T> list) {
        if (list == null || list.isEmpty()) {
            return;
        }
        AtomicInteger row = new AtomicInteger(-1);
        AtomicInteger col = new AtomicInteger(-1);
        for (AtomicInteger i = new AtomicInteger(0); i.get() < sheet.getLastRowNum(); i.incrementAndGet()) {
            sheet.getRow(i.get()).cellIterator().forEachRemaining(cell -> {
                if (cell.getCellType().equals(CellType.STRING)) {
                    if (cell.getStringCellValue().equals(placeholder)) {
                        row.set(i.get());
                        col.set(cell.getColumnIndex());
                    }
                }
            });
        }
        boolean first = true;
        for (T item : list) {
            int colNum = col.get();
            if (first) {
                sheet.shiftRows(
                        row.get(),          // 起始行
                        sheet.getLastRowNum(), // 结束行（最后一行）
                        1,                 // 向下移动 1 行
                        true,              // 是否复制行高
                        true               // 是否重置被移动行的样式
                );
                first = false;
            }
            Row sheetRow = sheet.createRow(row.get());
            for (Field field : item.getClass().getDeclaredFields()) {
                field.setAccessible(true);
                try {
                    Cell cell = sheetRow.createCell(colNum++);
                    cell.setCellValue(field.get(item).toString());
                } catch (Exception e) {
                    e.printStackTrace();
                }
                field.setAccessible(false);
            }
            row.incrementAndGet();
        }
    }

    /**
     * 给sheet添加下拉框数据验证
     *
     * @param sheet         表格
     * @param rowStartIndex 起始行
     * @param rowEndIndex   结束行
     * @param options       下拉框选项(k=列表索引,v=列表数据)
     */
    public static void addDataValidation(Sheet sheet, Integer rowStartIndex, Integer rowEndIndex, Integer col, List<String> options) {
        if (sheet instanceof XSSFSheet) {
            DataValidationHelper helper = sheet.getDataValidationHelper();

            DataValidationConstraint dvc = new XSSFDataValidationConstraint(options.toArray(new String[0]));
            // Excel 行列索引从0开始
            CellRangeAddressList cral = new CellRangeAddressList(rowStartIndex, rowEndIndex, col, col);
            DataValidation validation = helper.createValidation(dvc, cral);
            validation.setShowErrorBox(true);

            sheet.addValidationData(validation);
        }
    }

    /**
     * 创建名称管理器
     *
     * @param workbook        表格
     * @param title           名称
     * @param refersToFormula 参考"Data!$A$2:$A$4"
     */
    public static void addName(Workbook workbook, String title, String refersToFormula) {
        Name name = workbook.createName();
        name.setRefersToFormula(refersToFormula);
        name.setNameName(title);
    }

    /**
     * 在表格中查找指定文本
     *
     * @param sheet      表格
     * @param searchText 搜索文本
     * @param ignoreCase 是否忽略大小写
     * @return 找到的Cell
     */
    public static Cell findFirstCellInSheet(Sheet sheet, String searchText, boolean ignoreCase) {
        if (searchText == null) return null;
        for (Row row : sheet) {
            if (row == null) continue;
            for (Cell cell : row) {
                if (cell == null) continue;

                String cellValue = getCellValueAsString(cell);
                if (cellValue == null) continue;

                boolean match;
                if (ignoreCase) {
                    match = cellValue.equalsIgnoreCase(searchText);
                } else {
                    match = cellValue.equals(searchText);
                }

                if (match) {
                    return cell;
                }
            }
        }
        return null;
    }

    /**
     * 将 Cell 的值统一转为字符串（处理字符串、数字、布尔、公式等）
     */
    private static String getCellValueAsString(Cell cell) {
        DataFormatter formatter = new DataFormatter(); // 用于格式化数字/日期等
        try {
            // 如果是公式，尝试获取计算后的值（需 FormulaEvaluator）
            if (cell.getCellType() == CellType.FORMULA) {
                Workbook wb = cell.getRow().getSheet().getWorkbook();
                CreationHelper helper = wb.getCreationHelper();
                FormulaEvaluator evaluator = helper.createFormulaEvaluator();
                CellValue cellValue = evaluator.evaluate(cell);
                switch (cellValue.getCellType()) {
                    case STRING:
                        return cellValue.getStringValue();
                    case NUMERIC:
                        return formatter.formatCellValue(cell); // 保留格式（如日期）
                    case BOOLEAN:
                        return String.valueOf(cellValue.getBooleanValue());
                    default:
                        return "";
                }
            } else {
                return formatter.formatCellValue(cell);
            }
        } catch (Exception e) {
            // 公式错误或无法计算时，回退到原始值
            return formatter.formatCellValue(cell);
        }
    }

    /**
     * 给表格列设置背景颜色
     *
     * @param workbook 工作簿
     * @param sheet    表格
     * @param startRow 起始行
     * @param endRow   结束行
     * @param startCol 起始列
     * @param endCol   结束列
     */
    public static void colorColumn(Workbook workbook, Sheet sheet, int startRow, int endRow, int startCol, int endCol) {
        List<CellStyle> styles = new ArrayList<>();
        for (int i = 0; i < 6; i++) {
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            styles.add(cellStyle);
        }
        styles.get(0).setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); // 浅蓝
        styles.get(1).setFillForegroundColor(IndexedColors.LIME.getIndex()); // 淡绿
        styles.get(2).setFillForegroundColor(IndexedColors.YELLOW.getIndex()); // 黄色
        styles.get(3).setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex()); // 天蓝
        styles.get(4).setFillForegroundColor(IndexedColors.ROSE.getIndex()); // 玫瑰红
        styles.get(5).setFillForegroundColor(IndexedColors.ORANGE.getIndex()); // 橙色

        for (int colIndex = startCol; colIndex < endCol; colIndex++) {
            CellStyle currentCellStyle = styles.get(colIndex % 6);
            for (int i = startRow; i <= endRow; i++) {
                // 设置起始行和起始列（0-based）
                Row row = sheet.getRow(i);
                Cell cell = row.getCell(colIndex);
                if (cell == null) {
                    cell = row.createCell(colIndex);
                }
                CellStyle cellStyle = cell.getCellStyle();
                CellStyle newStyle = workbook.createCellStyle();
                newStyle.cloneStyleFrom(cellStyle);
                newStyle.setFillForegroundColor(currentCellStyle.getFillForegroundColor());
                newStyle.setFillPattern(currentCellStyle.getFillPattern());
                cell.setCellStyle(newStyle);
            }
        }
    }
}
